package com.jwb.pub.daoImpl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jwb.pub.dao.BaseDAO;
import com.jwb.pub.model.JavaModel;
import com.jwb.pub.model.JavaSql;
import com.jwb.pub.model.Page;
import com.jwb.util.ToolsUtil;
import org.apache.ibatis.session.SqlSession;
//import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.io.Reader;
import java.io.StringReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


@Component
public class BaseDAOImpl extends MybatisSessionFactory implements BaseDAO {
//	private Logger logger = Logger.getLogger(BaseDAOImpl.class);

	/**
	 * 返回数据类型
	 */
	public String getDataBaseNameInUppercase() {
		// Session session = null;
		Connection con = null;
		SqlSession  session = null;
		try {
			
		    session = getSession();
			con=session.getConnection();			
			String name = con.getMetaData().getDatabaseProductName().toUpperCase();

			// session.flush();
			// session.clear();
			return name;
		} catch (Exception e) {
			e.printStackTrace();
//			logger.error("getDataBaseName()：获取数据库名称异常", e);
		} finally {
			try {
				con.close();
				closeSession(session);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return null;
	}
	
	
	public int findBySqlCnt(String strsql) throws Exception
	{
		
		int sqlCnt=-1;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		SqlSession session = null;
		
		try
		{
			System.out.println("findBySqlCnt-sql "+ strsql);
			session = this.getSession();
			conn = session.getConnection();
			ps = conn.prepareStatement(strsql);
			rs = ps.executeQuery();
			ResultSetMetaData rsMeta = rs.getMetaData(); // 列数据对
			int colCnt = rsMeta.getColumnCount(); // 列的总个数
			int j=0;
			while (rs.next())
			{
				JSONObject jso = new JSONObject();
				for (int i = 1; i <= colCnt; i++)
				{
					
					
				    String colName=rsMeta.getColumnLabel(i).toLowerCase();
				    sqlCnt=rs.getInt(rsMeta.getColumnLabel(i));			   
					
				}
				
			}
			
			
		}
		catch (Exception e)
		{
			
			 e.printStackTrace();
		}
		finally
		{
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {

			}
			this.closeSession(session);
		}
		return sqlCnt;
	}
	

    /**
     * table 转 json
     * @param strsql
     * @return
     * @throws Exception
     */
    
	public JSONObject findBySqlToJosn(String strsql) throws Exception
	{
		
		JSONObject jsobject = new JSONObject();
		JSONArray jsarry = new JSONArray();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		SqlSession session = null;
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "数据获取异常");
		try
		{
			
			System.out.println("findBySqlToJosn-sql "+ strsql);
			session = this.getSession();
			conn = session.getConnection();
			ps = conn.prepareStatement(strsql);
			rs = ps.executeQuery();
			ResultSetMetaData rsMeta = rs.getMetaData(); // 列数据对
			int colCnt = rsMeta.getColumnCount(); // 列的总个数
			int j=0;
			while (rs.next())
			{
				JSONObject jso = new JSONObject();
				for (int i = 1; i <= colCnt; i++)
				{
					
					String colType=rsMeta.getColumnTypeName(i);
				    String colName=rsMeta.getColumnLabel(i).toLowerCase();
				    //String colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnName(i)));	
				    String colValue="";				    
				    if("CLOB".equals(colType))
				    {
				    	Clob clob = rs.getClob(rsMeta.getColumnLabel(i));//java.sql.Clob				    	
				    	if(clob != null){
				    		colValue = clob.getSubString((long)1,(int)clob.length());
				    	}
				    }
				    else
				    {
				    	 colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnLabel(i)));	
   			        } 
				    
				   // System.out.println("colType="+colType+";"+colName+"="+colValue);	
					
				   try
				   {
						JSONObject jsotmp = new JSONObject();
						jsotmp=JSONObject.parseObject(colValue);
						if(jsotmp==null)
						{
							jso.put(colName, "");
						}
						else
						{
							jso.put(colName, jsotmp);
						}
						
				   }
				   catch (Exception e)
				   {
					   jso.put(colName, colValue);
						//jso.put("colType", colType);
				   }
				  
				    
				    
				 
					
				}
				jsarry.add(j++, jso);
			}
			jsobject.put("respCode", "0");
			jsobject.put("respDesc", "数据获取成功");
			jsobject.put("list", jsarry);
			
		}
		catch (Exception e)
		{
			
			 e.printStackTrace();
		}
		finally
		{
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {

			}
			this.closeSession(session);
		}
		return jsobject;
	}
	
	
	
	
	public JSONObject findBySqlToJosn(String strsql,String listname) throws Exception
	{
		
		JSONObject jsobject = new JSONObject();
		JSONArray jsarry = new JSONArray();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		SqlSession session = null;
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "数据获取异常");
		try
		{
			
			System.out.println("findBySqlToJosn-sql "+ strsql);
			session = this.getSession();
			conn = session.getConnection();
			ps = conn.prepareStatement(strsql);
			rs = ps.executeQuery();
			ResultSetMetaData rsMeta = rs.getMetaData(); // 列数据对
			int colCnt = rsMeta.getColumnCount(); // 列的总个数
			int j=0;
			while (rs.next())
			{
				JSONObject jso = new JSONObject();
				for (int i = 1; i <= colCnt; i++)
				{
					
					String colType=rsMeta.getColumnTypeName(i);
				    String colName=rsMeta.getColumnLabel(i).toLowerCase();
				  //  String colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnName(i)));
				    String colValue="";				    
				    if("CLOB".equals(colType))
				    {
				    	Clob clob = rs.getClob(rsMeta.getColumnLabel(i));//java.sql.Clob				    	
				    	if(clob != null){
				    		colValue = clob.getSubString((long)1,(int)clob.length());
				    	}
				    }
				    else
				    {
				    	 colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnLabel(i)));	
   			       } 
				    //System.out.println("colType="+colType+";"+colName+"="+colValue);	
				    try
					   {
							JSONObject jsotmp = new JSONObject();
							jsotmp=JSONObject.parseObject(colValue);
							if(jsotmp==null)
							{
								jso.put(colName, "");
							}
							else
							{
								jso.put(colName, jsotmp);
							}
							
					   }
					   catch (Exception e)
					   {
						   jso.put(colName, colValue);
							//jso.put("colType", colType);
					   }
					
				}
				jsarry.add(j++, jso);
			}
			jsobject.put("respCode", "0");
			jsobject.put("respDesc", "数据获取成功");
			jsobject.put(listname, jsarry);
			
		}
		catch (Exception e)
		{
			
			 e.printStackTrace();
		}
		finally
		{
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {

			}
			this.closeSession(session);
		}
		return jsobject;
	}
	
	
	/**
	 * 实现分页查询
	 */
	
	public JSONObject findBySqlToJosn(String strsql, Page page) throws Exception
	{
		
		JSONObject jsobject = new JSONObject();
		JSONArray jsarry = new JSONArray();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		SqlSession session = null;
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "数据获取异常");
	 	jsobject.put("page", 	JSONObject.toJSON(page));
	 
		try
		{
			String sql = "select count(*) from ( " + strsql + ") tc ";
			int totalRecords=findBySqlCnt( sql);
			System.out.println("findBySqlToJosn-sql "+ strsql);
			page.setTotalRecords(totalRecords);
			if (page.getTotalRecords() >= 0) {
				page.formatPage();
				session = this.getSession();
				conn = session.getConnection();
				String startNum = String.valueOf((page.getCurrentPageNbr() - 1)
						* page.getEveryPageCnt() + 1);
				String endNum = String.valueOf(page.getCurrentPageNbr()
						* page.getEveryPageCnt());
				
				sql = " select nn.*" + " from (select rownum rowrec, mm.* from ("
						+ strsql + ") mm) nn" + " where nn.rowrec between "
						+ startNum + " and " + endNum;
				sql ="SELECT nn.* "+
					"	  FROM (SELECT  @rownum := @rownum +1 AS rowrec, mm.* "+
				    "      FROM ("+ strsql + ") mm ,(SELECT "+
				"    @rownum := 0) tr ) nn "+
				"    LIMIT  "+(page.getCurrentPageNbr() - 1)*page.getEveryPageCnt()+","+page.getEveryPageCnt();				
				
				System.out.println(sql);
				try
				{
					ps = conn.prepareStatement(sql);
					rs = ps.executeQuery();
					ResultSetMetaData rsMeta = rs.getMetaData(); // 列数据对
					int colCnt = rsMeta.getColumnCount(); // 列的总个数
					int j=0;
					while (rs.next())
					{
						JSONObject jso = new JSONObject();
						for (int i = 1; i <= colCnt; i++)
						{
							
							String colType=rsMeta.getColumnTypeName(i);
						    String colName=rsMeta.getColumnLabel(i).toLowerCase();
						    //String colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnName(i)));
							//System.out.println("colType="+colType+";"+colName+"="+colValue);	
						    String colValue="";				    
						    if("CLOB".equals(colType))
						    {
						    	Clob clob = rs.getClob(rsMeta.getColumnLabel(i));//java.sql.Clob				    	
						    	if(clob != null){
						    		colValue = clob.getSubString((long)1,(int)clob.length());
						    	}
						    }
						    else
						    {
						    	 colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnLabel(i)));	
		   			       } 
						   try
						   {
								JSONObject jsotmp = new JSONObject();
								jsotmp=JSONObject.parseObject(colValue);								
								if(jsotmp==null)
								{
									jso.put(colName, "");
								}
								else
								{
									jso.put(colName, jsotmp);
								}
								
								
						   }
						   catch (Exception e)
						   {
							   jso.put(colName, colValue);
							  // System.out.println("colType="+colType+";"+colName+"="+colValue);	
								//jso.put("colType", colType);
						   }
							
						}
						jsarry.add(j++, jso);
					}
					jsobject.put("respCode", "0");
					jsobject.put("respDesc", "数据获取成功");
					jsobject.put("page", JSONObject.toJSON(page));
					jsobject.put("list", jsarry);
					
				}
				catch(Exception e)
				{
					
				}
				
				
			}
			
			
			
			
		}
		catch (Exception e)
		{
			
			 e.printStackTrace();
		}
		finally
		{
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {

			}
			this.closeSession(session);
		}
		return jsobject;
	}
	
	
	
	/**
	 * 实现分页查询
	 */
	
	public JSONObject findBySqlToJosn(String strsql, Page page,String listname) throws Exception
	{
		
		JSONObject jsobject = new JSONObject();
		JSONArray jsarry = new JSONArray();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		SqlSession session = null;
		jsobject.put("respCode", "-1");
		jsobject.put("respDesc", "数据获取异常");
	 	jsobject.put("page", JSONObject.toJSON(page));
		try
		{
			String sql = "select count(*) from ( " + strsql + ") tc ";
			int totalRecords=findBySqlCnt( sql);
			System.out.println("findBySqlToJosn-sql "+ strsql);
			page.setTotalRecords(totalRecords);
			if (page.getTotalRecords() >= 0) {
				page.formatPage();
				session = this.getSession();
				conn = session.getConnection();
				String startNum = String.valueOf((page.getCurrentPageNbr() - 1)
						* page.getEveryPageCnt() + 1);
				String endNum = String.valueOf(page.getCurrentPageNbr()
						* page.getEveryPageCnt());
//				sql = " select nn.*" + " from (select rownum rowrec, mm.* from ("
//						+ strsql + ") mm) nn" + " where nn.rowrec between "
//						+ startNum + " and " + endNum;
				sql ="SELECT nn.* "+
						"	  FROM (SELECT  @rownum := @rownum +1 AS rowrec, mm.* "+
					    "      FROM ("+ strsql + ") mm ,(SELECT "+
					"    @rownum := 0) tr ) nn "+
					"    LIMIT  "+(page.getCurrentPageNbr() - 1)*page.getEveryPageCnt()+","+page.getEveryPageCnt();				
					
				System.out.println(sql);
				try
				{
					ps = conn.prepareStatement(sql);
					rs = ps.executeQuery();
					ResultSetMetaData rsMeta = rs.getMetaData(); // 列数据对
					int colCnt = rsMeta.getColumnCount(); // 列的总个数
					int j=0;
					while (rs.next())
					{
						JSONObject jso = new JSONObject();
						for (int i = 1; i <= colCnt; i++)
						{
							
							String colType=rsMeta.getColumnTypeName(i);
						    String colName=rsMeta.getColumnLabel(i).toLowerCase();
						    //String colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnName(i)));
							//System.out.println("colType="+colType+";"+colName+"="+colValue);	
						    String colValue="";				    
						    if("CLOB".equals(colType))
						    {
						    	Clob clob = rs.getClob(rsMeta.getColumnLabel(i));//java.sql.Clob				    	
						    	if(clob != null){
						    		colValue = clob.getSubString((long)1,(int)clob.length());
						    	}
						    }
						    else
						    {
						    	 colValue=ToolsUtil.nullToStr(rs.getString(rsMeta.getColumnLabel(i)));	
		   			       } 
						    try
							   {
									JSONObject jsotmp = new JSONObject();
									jsotmp=JSONObject.parseObject(colValue);
									if(jsotmp==null)
									{
										jso.put(colName, "");
									}
									else
									{
										jso.put(colName, jsotmp);
									}
									
							   }
							   catch (Exception e)
							   {
								   jso.put(colName, colValue);
									//jso.put("colType", colType);
							   }
							
						}
						jsarry.add(j++, jso);
					}
					jsobject.put("respCode", "0");
					jsobject.put("respDesc", "数据获取成功");
					jsobject.put("page", JSONObject.toJSON(page));
					jsobject.put(listname, jsarry);
					
				}
				catch(Exception e)
				{
					
				}
				
				
			}
			
			
			
			
		}
		catch (Exception e)
		{
			
			 e.printStackTrace();
		}
		finally
		{
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {

			}
			this.closeSession(session);
		}
		return jsobject;
	}
	
	
	
	/**
	 * 预处理
	 * @param strsql
	 * @param list
	 * @return
	 */
	public boolean excuteSql(String strsql,ArrayList<JavaModel> list)
	{
		PreparedStatement stmt = null;
		Connection conn = null;
		SqlSession session = null;
      
		boolean commitflag = false;
		try
		{   
			System.out.println("excuteSql-sql JavaModel "+ strsql);
			session = this.getSession();
			conn= session.getConnection();			
			stmt=conn.prepareStatement(strsql);
			if(list!=null)
			{
			        int i=0;			
					for(JavaModel jm:list)
					{
					   int parameterIndex=i+1;
					  
					   String typeClassName=jm.getTypeClassName();
					   System.out.println(typeClassName);
					   
					   if (typeClassName.equals("clob")) {
						    String  x= (String) jm.getObject();
							Reader sqlClob = new StringReader(x); 
							stmt.setCharacterStream(1, sqlClob, x.length());
							
						   
						  
					   }
					   
					   if (typeClassName.equals("class java.io.StringReader")) {
						    Reader x= (StringReader) jm.getObject();
						    String cont=ToolsUtil.read(x);						    
						    Reader sqlClob= new StringReader(cont);	
						    System.out.println(cont+" "+cont.length());
						    stmt.setCharacterStream(parameterIndex, sqlClob,cont.length());
						    x.close();
						    sqlClob.close();
						   
						  
					   }
					   if (typeClassName.equals("class java.lang.String")) {
						   String  x= (String) jm.getObject();
						   stmt.setString(parameterIndex, x);				  
					   }
					      
					   if (typeClassName.equals("class java.lang.Integer")) {
						   int  x= (Integer) jm.getObject();
						   stmt.setInt(parameterIndex, x);			  
					   }	
					   
					   if (typeClassName.equals("class java.lang.Long")) {
						   Long  x= (Long) jm.getObject();
						   stmt.setLong(parameterIndex, x);			  
					   }	
					   
					   if (typeClassName.equals("class java.lang.Float")) {
						   Float  x= (Float) jm.getObject();
						   stmt.setFloat(parameterIndex, x);			  
					   }	
					   
					   if (typeClassName.equals("class java.lang.Double")) {
						   Double  x= (Double) jm.getObject();
						   stmt.setDouble(parameterIndex, x);		   
					   }
					   
					   if(typeClassName.equals("class java.util.Date")){
//						   Date x = new Date(((java.util.Date) jm.getObject()).getTime());
//						   stmt.setDate(parameterIndex, x);
						   Timestamp x = new Timestamp(((java.util.Date) jm.getObject()).getTime());
						   stmt.setTimestamp(parameterIndex, x);
					   }
					   
					   if(typeClassName.equals("class java.sql.Date")){
						   Date x = (Date) jm.getObject();
						   stmt.setDate(parameterIndex, x);
					   }
					   
					   i++;
					   
					}
			}
			stmt.executeUpdate();
			
			
			
			commitflag = true;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try {
				
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
				if (session != null) {
					session.close();
				}
			} catch (Exception e) {

			}
			this.closeSession(session);
		}
		
		return commitflag;
	}
	
	
	public boolean excuteBatchSql(Map<String,ArrayList<JavaModel>> map){
		  boolean commitflag = false;
		  SqlSession session = null;
		  Connection conn = null;
		  if(!map.isEmpty()){
			  List<PreparedStatement> pss = new ArrayList<>();
			  try{
				  session = this.getSession();
				  conn = session.getConnection();
				  conn.setAutoCommit(false);
				  for(String sql:map.keySet()){
					  PreparedStatement ps = null;
					  ps= conn.prepareStatement(sql);	
					  ArrayList<JavaModel> list = map.get(sql);
					  int idx=1;
					  if(list!=null){		
						  for(JavaModel jm:list){
							 String typeClassName=jm.getTypeClassName();
							 System.out.println(typeClassName);
							 if (typeClassName.equals("clob")) {
								 String  x= (String) jm.getObject();
								 Reader sqlClob = new StringReader(x); 
								 ps.setCharacterStream(idx, sqlClob, x.length());									  
							 }
							 if (typeClassName.equals("class java.io.StringReader")) {
								Reader x= (StringReader) jm.getObject();
								String cont=ToolsUtil.read(x);						    
								Reader sqlClob= new StringReader(cont);	
								System.out.println(cont+" "+cont.length());
							    ps.setCharacterStream(idx, sqlClob,cont.length());
								x.close();
								sqlClob.close();
							}
							if (typeClassName.equals("class java.lang.String")) {
								String  x= (String) jm.getObject();
							    ps.setString(idx, x);				  
							}
							if (typeClassName.equals("class java.lang.Integer")) {
								int  x= (Integer) jm.getObject();
								ps.setInt(idx, x);			  
						    }	
							if (typeClassName.equals("class java.lang.Long")) {
								Long  x= (Long) jm.getObject();
								ps.setLong(idx, x);			  
							}	
							if (typeClassName.equals("class java.lang.Float")) {
								Float  x= (Float) jm.getObject();
								ps.setFloat(idx, x);			  
							}	
							if (typeClassName.equals("class java.lang.Double")) {
								Double  x= (Double) jm.getObject();
								ps.setDouble(idx, x);			  
							}
							
							if(typeClassName.equals("class java.util.Date")){
								   Timestamp x = new Timestamp(((java.util.Date) jm.getObject()).getTime());
								   ps.setTimestamp(idx, x);
						     }
							   
							if(typeClassName.equals("class java.sql.Date")){
								   Date x = (Date) jm.getObject();
								   ps.setDate(idx, x);
							}
							
							idx++;	   
						}
						  
					  }
					  
					  ps.addBatch();
					  pss.add(ps);
				  }
				  
				  for(PreparedStatement ps:pss){
					  ps.executeBatch();
				  }
				  conn.commit();
				  commitflag =true;
			  }catch(Exception e){
				  e.printStackTrace();
				  try{
					conn.rollback();
				  }catch(SQLException e1){
					e1.printStackTrace();
				  }
			  }finally{
				  try {
					  	for(PreparedStatement ps:pss){
					  		if(ps!=null){
					  			ps.close();
					  		}
					  	}
					  	if (conn != null) {
							conn.close();
						}
						if (session != null) {
							session.close();
						}

				  } catch (Exception e) {
					  e.printStackTrace();
				  }
				  this.closeSession(session);
			  }

		  }
		  return commitflag;
	}
	
	public boolean excuteBatchOrderedSql(LinkedHashMap<String,ArrayList<JavaModel>> map){
		  boolean commitflag = false;
		  SqlSession session = null;
		  Connection conn = null;
		  if(!map.isEmpty()){
			  List<PreparedStatement> pss = new ArrayList<>();
			  try{
				  session = this.getSession();
				  conn = session.getConnection();
				  conn.setAutoCommit(false);
				  for(String sql:map.keySet()){
					  PreparedStatement ps = null;
					  ps= conn.prepareStatement(sql);	
					  ArrayList<JavaModel> list = map.get(sql);
					  int idx=1;
					  if(list!=null){		
						  for(JavaModel jm:list){
							 String typeClassName=jm.getTypeClassName();
							 System.out.println(typeClassName);
							 if (typeClassName.equals("clob")) {
								 String  x= (String) jm.getObject();
								 Reader sqlClob = new StringReader(x); 
								 ps.setCharacterStream(idx, sqlClob, x.length());									  
							 }
							 if (typeClassName.equals("class java.io.StringReader")) {
								Reader x= (StringReader) jm.getObject();
								String cont=ToolsUtil.read(x);						    
								Reader sqlClob= new StringReader(cont);	
								System.out.println(cont+" "+cont.length());
							    ps.setCharacterStream(idx, sqlClob,cont.length());
								x.close();
								sqlClob.close();
							}
							if (typeClassName.equals("class java.lang.String")) {
								String  x= (String) jm.getObject();
							    ps.setString(idx, x);				  
							}
							if (typeClassName.equals("class java.lang.Integer")) {
								int  x= (Integer) jm.getObject();
								ps.setInt(idx, x);			  
						    }	
							if (typeClassName.equals("class java.lang.Long")) {
								Long  x= (Long) jm.getObject();
								ps.setLong(idx, x);			  
							}	
							if (typeClassName.equals("class java.lang.Float")) {
								Float  x= (Float) jm.getObject();
								ps.setFloat(idx, x);			  
							}	
							if (typeClassName.equals("class java.lang.Double")) {
								Double  x= (Double) jm.getObject();
								ps.setDouble(idx, x);			  
							}
							
							if(typeClassName.equals("class java.util.Date")){
								   Timestamp x = new Timestamp(((java.util.Date) jm.getObject()).getTime());
								   ps.setTimestamp(idx, x);
						     }
							   
							if(typeClassName.equals("class java.sql.Date")){
								   Date x = (Date) jm.getObject();
								   ps.setDate(idx, x);
							}
							
							idx++;	   
						}
						  
					  }
					  
					  ps.addBatch();
					  pss.add(ps);
				  }
				  
				  for(PreparedStatement ps:pss){
					  ps.executeBatch();
				  }
				  conn.commit();
				  commitflag =true;
			  }catch(Exception e){
				  e.printStackTrace();
				  try{
					conn.rollback();
				  }catch(SQLException e1){
					e1.printStackTrace();
				  }
			  }finally{
				  try {
					  	for(PreparedStatement ps:pss){
					  		if(ps!=null){
					  			ps.close();
					  		}
					  	}
					  	if (conn != null) {
							conn.close();
						}
						if (session != null) {
							session.close();
						}

				  } catch (Exception e) {
					  e.printStackTrace();
				  }
				  this.closeSession(session);
			  }

		  }
		  return commitflag;
	}
	
	public boolean excuteBatchOrderedSqlEx(LinkedHashMap<JavaSql,ArrayList<JavaModel>> map){
		  boolean commitflag = false;
		  SqlSession session = null;
		  Connection conn = null;
		  if(!map.isEmpty()){
			  List<PreparedStatement> pss = new ArrayList<>();
			  try{
				  session = this.getSession();
				  conn = session.getConnection();
				  conn.setAutoCommit(false);
				  for(JavaSql jsql:map.keySet()){
					  String sql = jsql.getSql();
					  PreparedStatement ps = null;
					  ps= conn.prepareStatement(sql);	
					  ArrayList<JavaModel> list = map.get(jsql);
					  int idx=1;
					  if(list!=null){		
						  for(JavaModel jm:list){
							 String typeClassName=jm.getTypeClassName();
							 System.out.println(typeClassName);
							 if (typeClassName.equals("clob")) {
								 String  x= (String) jm.getObject();
								 Reader sqlClob = new StringReader(x); 
								 ps.setCharacterStream(idx, sqlClob, x.length());									  
							 }
							 if (typeClassName.equals("class java.io.StringReader")) {
								Reader x= (StringReader) jm.getObject();
								String cont=ToolsUtil.read(x);						    
								Reader sqlClob= new StringReader(cont);	
								System.out.println(cont+" "+cont.length());
							    ps.setCharacterStream(idx, sqlClob,cont.length());
								x.close();
								sqlClob.close();
							}
							if (typeClassName.equals("class java.lang.String")) {
								String  x= (String) jm.getObject();
							    ps.setString(idx, x);				  
							}
							if (typeClassName.equals("class java.lang.Integer")) {
								int  x= (Integer) jm.getObject();
								ps.setInt(idx, x);			  
						    }	
							if (typeClassName.equals("class java.lang.Long")) {
								Long  x= (Long) jm.getObject();
								ps.setLong(idx, x);			  
							}	
							if (typeClassName.equals("class java.lang.Float")) {
								Float  x= (Float) jm.getObject();
								ps.setFloat(idx, x);			  
							}	
							if (typeClassName.equals("class java.lang.Double")) {
								Double  x= (Double) jm.getObject();
								ps.setDouble(idx, x);			  
							}
							
							if(typeClassName.equals("class java.util.Date")){
								   Timestamp x = new Timestamp(((java.util.Date) jm.getObject()).getTime());
								   ps.setTimestamp(idx, x);
						     }
							   
							if(typeClassName.equals("class java.sql.Date")){
								   Date x = (Date) jm.getObject();
								   ps.setDate(idx, x);
							}
							   
							idx++;	   
						}
						  
					  }
					  
					  ps.addBatch();
					  pss.add(ps);
				  }
				  
				  for(PreparedStatement ps:pss){
					  ps.executeBatch();
				  }
				  conn.commit();
				  commitflag =true;
			  }catch(Exception e){
				  e.printStackTrace();
				  try{
					conn.rollback();
				  }catch(SQLException e1){
					e1.printStackTrace();
				  }
			  }finally{
				  try {
					  	for(PreparedStatement ps:pss){
					  		if(ps!=null){
					  			ps.close();
					  		}
					  	}
					  	if (conn != null) {
							conn.close();
						}
						if (session != null) {
							session.close();
						}

				  } catch (Exception e) {
					  e.printStackTrace();
				  }
				  this.closeSession(session);
			  }

		  }
		  return commitflag;
	}
	
	public JSONObject excuteProSql(String strpro,List<String> pamList) {
		JSONObject jsobject = new JSONObject();		
		SqlSession session = null;
		String code ="-1";
	    String codeDesc="系统异常";
		Connection con= null;
		String req="";
		CallableStatement proc = null;
		int i = 0; 
		int j = 0; 
		try {
			session = this.getSession();					
	        con = session.getConnection();
	        if(pamList.size()>0){
	        	int size= pamList.size();
	        	req = ToolsUtil.repeatString("?", size);
				
	        }
	        
			proc=con.prepareCall("{call "+strpro+"("+req+"?,?)}"); 
			for(i=0;i < pamList.size(); i++)  
	        {
			   proc.setString(i+1, pamList.get(i).toString());
			   
	        }
			
			proc.registerOutParameter(i+1, Types.VARCHAR);
	        proc.registerOutParameter(i+2, Types.VARCHAR);
			
			proc.executeUpdate();
			//proc.execute();
			
			code = proc.getString(i+1);
			codeDesc = proc.getString(i+2);
			
			
		} catch (Exception e) {
			code ="-1";
			codeDesc = e.getMessage();
			e.printStackTrace();
		} finally {
			try {
				
				proc.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			this.closeSession(session);
			jsobject.put("respCode", code);
			jsobject.put("respDesc", codeDesc);
			
		}
		return jsobject;
	}
	
	/**
	 * String setSql= "total_cur_step=?,total_cur_ex_step=?";
       String whereSql = "openid = 'owFeQ4rw1yaq3sx4MUm4l4Ccl200' ";
       ArrayList<JavaModel> params = new ArrayList<>();
       params.add(new JavaModel(300));
       params.add(new JavaModel(500));
       baseDAO.replaceSql("app_feet", whereSql, setSql, params);
	 */
	public boolean replaceSql(String tableName,String whereSql,String setSql,ArrayList<JavaModel> params){
		boolean done = false;
		try{
			String sql = "SELECT COUNT(*) FROM "+tableName+" WHERE "+whereSql;
			int num = findBySqlCnt(sql);
			if(num==0){
				sql = "INSERT INTO "+tableName+" SET "+whereSql+","+setSql;
			}else{
				sql = "UPDATE "+tableName+" SET "+setSql+" WHERE "+whereSql;
			}
			done = excuteSql(sql, params);			
		}catch(Exception e){
			e.printStackTrace();
		}		
		return done;
	}
	
	
	public int getMaxId(String table , String keyId)
	{
		return this.getMaxId(table, keyId, "");
	}
	
	public int getMaxId(String table , String keyId,String condition)
	{
		JSONObject jsobject = new JSONObject();
		int newId=0;	    
	    String sql="SELECT IFNULL(MAX("+keyId+"),0)+1  AS newid FROM "+table+" "+condition;
	    try
      	{ 
	      jsobject=this.findBySqlToJosn(sql);
	      if ("0".equals(jsobject.getString("respCode"))) {
	    	  JSONArray list = jsobject.getJSONArray("list");
	    	  newId=list.getJSONObject(0).getInteger("newid");
	      }
      	}
	    catch(Exception e)
      	{
      		
      	}
      	
		return newId;
	}
	
	/**
	 * 
	 * @param dbName service
	 * @param tableName user_dict
	 * @return
	 */
	public int getSeqId(String dbName , String tableName)
	{
		 	
		JSONObject jsobject = new JSONObject();
		int newId=0;	    
	    String sql="SELECT AUTO_INCREMENT  AS newid  FROM information_schema.tables WHERE table_schema='"+dbName+"' AND table_name='"+tableName+"'";
	    try
      	{ 
	      jsobject=this.findBySqlToJosn(sql);
	      if ("0".equals(jsobject.getString("respCode"))) {
	    	  JSONArray list = jsobject.getJSONArray("list");
	    	  newId=list.getJSONObject(0).getInteger("newid");
	      }
      	}
	    catch(Exception e)
      	{
      		
      	}
      	
		return newId;
	}
}
